#include "MysqlDao.hpp"
#include "Defer.hpp"
#include "const.hpp"
#include "data.hpp"
#include <chrono>
#include <jdbc/cppconn/driver.h>
#include <jdbc/cppconn/exception.h>
#include <jdbc/cppconn/prepared_statement.h>
#include <jdbc/cppconn/resultset.h>
#include <jdbc/cppconn/statement.h>
#include <jdbc/mysql_connection.h>
#include <jdbc/mysql_driver.h>
#include <json/value.h>
#include <memory>
#include <mutex>
#include <ostream>
#include <sstream>
#include <string>
#include <thread>
#include <utility>

SqlConnect::SqlConnect(sql::Connection *con, int64_t lastTime)
    : _con(con), _last_oper_time(lastTime) {}

MysqlPool::MysqlPool(const std::string &url, const std::string &user,
                     const std::string &pass, const std::string &schema,
                     int poolSize)
    : _url(url), _user(user), _passwd(pass), _schema(schema),
      _poolSize(poolSize), _b_stop(false) {
  try {
    for (int i = 0; i < _poolSize; ++i) {
      sql::mysql::MySQL_Driver *driver =
          sql::mysql::get_mysql_driver_instance();
      auto *con = driver->connect(_url, _user, _passwd);
      con->setSchema(_schema);
      // 获取当前时间戳
      auto currentTime = std::chrono::system_clock::now().time_since_epoch();
      // 将时间戳转换为秒
      long long timeStamp =
          std::chrono::duration_cast<std::chrono::seconds>(currentTime).count();
      _pool.push(std::make_unique<SqlConnect>(con, timeStamp));
    }
    _check_thread = std::thread([this] {
      while (!_b_stop) {
        checkConnection();
        std::this_thread::sleep_for(std::chrono::seconds(60));
      }
    });
    _check_thread.detach();
  } catch (sql::SQLException &e) {
    std::cout << "mysql pool init failed, error is " << e.getSQLStateCStr()
              << std::endl;
    ;
  }
}

void MysqlPool::checkConnection() {
  std::lock_guard<std::mutex> lock(_mutex);
  int poolSize = _pool.size();
  auto currentTime = std::chrono::system_clock::now().time_since_epoch();
  long long timeTamp =
      std::chrono::duration_cast<std::chrono::seconds>(currentTime).count();
  for (int i = 0; i < poolSize; ++i) {
    int last_time = _pool.front()->_last_oper_time;
    if (timeTamp - last_time < 5) {
      continue;
    }
    auto con = std::move(_pool.front());
    _pool.pop();
    try {
      std::unique_ptr<sql::Statement> stmt(con->_con->createStatement());
      stmt->executeQuery("SELECT 1;");
      con->_last_oper_time = timeTamp;
      // std::cout << "execute timer alive query, cur is " << timeTamp
      //           << std::endl;
    } catch (sql::SQLException &e) {
      std::cout << "Error keeping connection alive: " << e.what() << std::endl;
      // 重新创建连接并替换旧连接
      sql::mysql::MySQL_Driver *driver =
          sql::mysql::get_mysql_driver_instance();
      auto *new_con = driver->connect(_url, _user, _passwd);
      new_con->setSchema(_schema);
      con->_con.reset(new_con);
      con->_last_oper_time = timeTamp;
    }
    _pool.push(std::move(con));
  }
}

std::unique_ptr<SqlConnect> MysqlPool::getConnection() {
  std::unique_lock<std::mutex> lock(_mutex);
  _cond.wait(lock, [this]() { return _b_stop || !_pool.empty(); });
  if (_b_stop) {
    return nullptr;
  }
  auto con = std::move(_pool.front());
  _pool.pop();
  return con;
}

void MysqlPool::returnConnect(std::unique_ptr<SqlConnect> con) {
  std::unique_lock<std::mutex> lock(_mutex);
  if (_b_stop) {
    return;
  }
  _pool.push(std::move(con));
  _cond.notify_one();
}

void MysqlPool::Close() {
  _b_stop = true;
  _cond.notify_all();
}

MysqlPool::~MysqlPool() {
  Close();
  std::lock_guard<std::mutex> lock(_mutex);
  while (!_pool.empty()) {
    _pool.pop();
  }
}

MysqlDao::MysqlDao() {
  const auto &host = config.get<std::string>("Mysql.Host");
  const auto &port = config.get<std::string>("Mysql.Port");
  const auto &user = config.get<std::string>("Mysql.User");
  const auto &password = config.get<std::string>("Mysql.PassWord");
  const auto &scheme = config.get<std::string>("Mysql.Scheme");
  const auto &poolSize = config.get<int>("Mysql.PoolSize");
  _pool.reset(
      new MysqlPool(host + ":" + port, user, password, scheme, poolSize));
}

MysqlDao::~MysqlDao() { _pool->Close(); }

// 用户注册逻辑
int MysqlDao::RegUser(const std::string &name, const std::string &email,
                      const std::string &pwd) {
  if (name.empty() || email.empty() || pwd.empty()) {
    std::cout << "name or email or pwd is empty." << std::endl;
    return -1;
  }
  auto con = _pool->getConnection();
  if (con == nullptr) {
    return -1;
  }
  Defer defer([this, &con]() {
    if (con.get() != nullptr) {
      con->_con->setAutoCommit(true);
      _pool->returnConnect(std::move(con));
    }
  });
  try {
    con->_con->setAutoCommit(true);
    // 查询用户名或者邮箱是否存在
    std::string sql = "SELECT * FROM user WHERE name= ? OR email= ?;";
    std::shared_ptr<sql::PreparedStatement> state(
        con->_con->prepareStatement(sql));
    state->setString(1, name);
    state->setString(2, email);
    std::shared_ptr<sql::ResultSet> res(state->executeQuery());
    // 查询到相关记录
    while (res->next()) {
      // 用户名存在
      if (res->getString("name") == name) {
        std::cout << "name exist." << std::endl;
        return -1;
      }
      // 邮箱存在
      if (res->getString("email") == email) {
        std::cout << "email exist." << std::endl;
        return -1;
      }
    }
    // 用户名和邮箱均不存在, 注册用户
    // 更新 user_id 表的 id字段
    sql = "SELECT id FROM user_id;";
    state.reset(con->_con->prepareStatement(sql));
    res.reset(state->executeQuery());
    if (!res->next()) {
      std::cout << "query user_id table failed." << std::endl;
      return -1;
    }
    con->_con->setAutoCommit(false);
    int uid = res->getInt64("id");
    sql = "UPDATE user_id SET id = ?;";
    state.reset(con->_con->prepareStatement(sql));
    state->setInt64(1, uid + 1);
    if (state->executeUpdate() == 0) {
      con->_con->rollback();
      std::cout << "user_id table update failed." << std::endl;
      return -1;
    }
    sql = "INSERT INTO user VALUES (NULL, ?, ?, ?, ?);";
    state.reset(con->_con->prepareStatement(sql));
    state->setInt64(1, uid + 1);
    state->setString(2, name);
    state->setString(3, email);
    state->setString(4, pwd);
    if (state->executeUpdate() == 0) {
      con->_con->rollback();
      std::cout << "insert failed." << std::endl;
      return -1;
    } else {
      con->_con->commit();
      return uid + 1;
    }
  } catch (sql::SQLException &e) {
    if (!con->_con->getAutoCommit()) {
      con->_con->rollback();
    }
    std::cerr << "SQLException: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ",SQLState: " << e.getSQLState() << " )" << std::endl;
    return -1;
  }
}

// 检查邮箱
bool MysqlDao::CheckEmail(const std::string &email) {
  // 获取mysql连接
  std::unique_ptr<SqlConnect> con = _pool->getConnection();
  if (con == nullptr) {
    return false;
  }
  Defer defer([this, &con]() {
    if (con.get() != nullptr) {
      _pool->returnConnect(std::move(con));
    }
  });
  try {
    // 查询用户名和邮箱是否存在
    std::string sql = "SELECT * FROM user WHERE email=?;";
    std::shared_ptr<sql::PreparedStatement> state(
        con->_con->prepareStatement(sql));
    state->setString(1, email);
    std::shared_ptr<sql::ResultSet> res(state->executeQuery());
    if (!res->next()) {
      // 邮箱和用户名不匹配
      std::cout << "Email does not match." << std::endl;
      return false;
    }
    return true;
  } catch (sql::SQLException &e) {
    std::cerr << "SQLException: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ",SQLState: " << e.getSQLState() << " )" << std::endl;
    return false;
  }
}

// 更新密码
bool MysqlDao::UpdatePwd(const std::string &email, const std::string &newPwd) {
  // 获取mysql连接
  std::unique_ptr<SqlConnect> con = _pool->getConnection();
  if (con == nullptr) {
    return false;
  }
  Defer defer([this, &con]() {
    if (con.get() != nullptr) {
      _pool->returnConnect(std::move(con));
    }
  });
  try {
    std::string sql = "UPDATE user SET pwd=? WHERE email=?;";
    std::shared_ptr<sql::PreparedStatement> state(
        con->_con->prepareStatement(sql));
    state->setString(1, newPwd);
    state->setString(2, email);
    if (state->executeUpdate() == 0) {
      std::cout << "update password failed." << std::endl;
      return false;
    }
    return true;
  } catch (sql::SQLException &e) {
    std::cerr << "SQLException: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ",SQLState: " << e.getSQLState() << " )" << std::endl;
    return false;
  }
}

bool MysqlDao::CheckPwd(const std::string &email, const std::string &pwd,
                        UserInfo &userInfo) {
  auto con = _pool->getConnection();
  if (con == nullptr) {
    return false;
  }
  Defer defer([this, &con]() {
    if (con.get() != nullptr) {
      _pool->returnConnect(std::move(con));
    }
  });
  try {
    std::string sql = "SELECT * FROM user WHERE email=? AND pwd=?;";
    std::shared_ptr<sql::PreparedStatement> state(
        con->_con->prepareStatement(sql));
    state->setString(1, email);
    state->setString(2, pwd);
    std::shared_ptr<sql::ResultSet> res(state->executeQuery());
    if (!res->next()) {
      return false;
    }
    userInfo.uid = res->getUInt("uid");
    userInfo.email = res->getString("email");
    userInfo.name = res->getString("name");
    userInfo.pwd = pwd;
    return true;
  } catch (sql::SQLException &e) {
    std::cerr << "SQLException: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ",SQLState: " << e.getSQLState() << " )" << std::endl;
    return false;
  }
}

bool MysqlDao::AddFriendApply(const FriendApplyInfo &friendApplyInfo) {
  auto con = _pool->getConnection();
  if (con == nullptr) {
    std::cout << "Mysql connection is null!" << std::endl;
    return false;
  }
  Defer defer([this, &con]() {
    if (con.get() != nullptr) {
      _pool->returnConnect(std::move(con));
    }
  });
  try {
    std::string sql =
        "INSERT INTO friend_apply(id,from_uid,to_uid,status,`desc`,remark)"
        "VALUES(NULL, ?, ?, ?, ?, ?);";
    std::shared_ptr<sql::PreparedStatement> state(
        con->_con->prepareStatement(sql));
    state->setInt(1, friendApplyInfo.from_uid);
    state->setInt(2, friendApplyInfo.to_uid);
    state->setInt(3, friendApplyInfo.status);
    state->setString(4, friendApplyInfo.desc);
    state->setString(5, friendApplyInfo.remark);
    int rows = state->executeUpdate();
    if (rows < 0) {
      return false;
    }
    return true;
  } catch (sql::SQLException &e) {
    std::cerr << "SQLException: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ",SQLState: " << e.getSQLState() << " )" << std::endl;
    return false;
  }
}

std::shared_ptr<UserInfo> MysqlDao::GetUser(int uid) {
  auto con = _pool->getConnection();
  if (con == nullptr) {
    return nullptr;
  }
  Defer defer([this, &con]() {
    if (con.get() != nullptr) {
      _pool->returnConnect(std::move(con));
    }
  });
  try {
    std::string sql = "SELECT * FROM user WHERE uid=?;";
    std::shared_ptr<sql::PreparedStatement> state(
        con->_con->prepareStatement(sql));
    state->setInt(1, uid);
    std::shared_ptr<sql::ResultSet> res(state->executeQuery());
    if (!res->next()) {
      return nullptr;
    }
    int uid = res->getUInt("uid");
    std::string email = res->getString("email");
    std::string name = res->getString("name");
    std::string pwd = res->getString("pwd");
    std::shared_ptr<UserInfo> userInfo = std::make_shared<UserInfo>();
    userInfo->name = name;
    userInfo->email = email;
    userInfo->uid = uid;
    userInfo->pwd = pwd;
    return userInfo;
  } catch (sql::SQLException &e) {
    std::cerr << "SQLException: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ",SQLState: " << e.getSQLState() << " )" << std::endl;
    return nullptr;
  }
}

std::shared_ptr<UserInfo> MysqlDao::GetUser(std::string name) {
  auto con = _pool->getConnection();
  if (con == nullptr) {
    return nullptr;
  }
  Defer defer([this, &con]() {
    if (con.get() != nullptr) {
      _pool->returnConnect(std::move(con));
    }
  });
  try {
    std::string sql = "SELECT * FROM user WHERE name=?;";
    std::shared_ptr<sql::PreparedStatement> state(
        con->_con->prepareStatement(sql));
    state->setString(1, name);
    std::shared_ptr<sql::ResultSet> res(state->executeQuery());
    if (!res->next()) {
      return nullptr;
    }
    int uid = res->getUInt("uid");
    std::string email = res->getString("email");
    std::string name = res->getString("name");
    std::string pwd = res->getString("pwd");
    std::shared_ptr<UserInfo> userInfo = std::make_shared<UserInfo>();
    userInfo->name = name;
    userInfo->email = email;
    userInfo->uid = uid;
    userInfo->pwd = pwd;
    return userInfo;
  } catch (sql::SQLException &e) {
    std::cerr << "SQLException: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ",SQLState: " << e.getSQLState() << " )" << std::endl;
    return nullptr;
  }
}

bool MysqlDao::GetApplyInfo(int to_uid,
                            std::vector<std::shared_ptr<FriendApplyInfo>> &list,
                            int start, int step) {
  auto con = _pool->getConnection();
  if (con == nullptr) {
    return false;
  }

  Defer defer([this, &con]() {
    if (con.get() != nullptr) {
      _pool->returnConnect(std::move(con));
    }
  });
  try {
    std::string sql =
        "SELECT * from user "
        "RIGHT JOIN (SELECT * FROM friend_apply WHERE "
        "friend_apply.to_uid=? AND friend_apply.status=0) AS apply ON "
        "user.uid=apply.from_uid "
        "ORDER BY apply.id ASC "
        "LIMIT ? OFFSET ?;";
    std::shared_ptr<sql::PreparedStatement> state(
        con->_con->prepareStatement(sql));
    state->setInt(1, to_uid);
    state->setInt(3, start);
    state->setInt(2, step);
    std::shared_ptr<sql::ResultSet> res(state->executeQuery());
    while (res->next()) {
      auto item = std::make_shared<FriendApplyInfo>();
      int to_uid = res->getInt("to_uid");
      int from_uid = res->getInt("from_uid");
      std::string desc = res->getString("desc");
      int status = res->getInt("status");
      std::string name = res->getString("name");
      // int sex;
      std::string email = res->getString("email");
      item->email = email;
      item->desc = desc;
      item->from_uid = from_uid;
      item->name = name;
      item->status = status;
      item->to_uid = to_uid;
      list.push_back(item);
    }
    return true;
  } catch (sql::SQLException &e) {
    std::cerr << "SQLException: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ",SQLState: " << e.getSQLState() << " )" << std::endl;
    return false;
  }
}

bool MysqlDao::MakeFriend(int fromuid, int touid, std::string remark) {
  // fromuid: 发起好友申请者uid
  // touid: 同意好友申请者uid
  // remark : 发送好友认证请求者对发起添加好友者的备注
  auto con = _pool->getConnection();
  if (con == nullptr) {
    return false;
  }
  Defer defer([this, &con]() {
    if (con.get() != nullptr) {
      con->_con->setAutoCommit(true);
      _pool->returnConnect(std::move(con));
    }
  });
  try {
    con->_con->setAutoCommit(false);
    // 更新好友申请列表
    std::string sql = "UPDATE friend_apply "
                      "SET status = 1 "
                      "WHERE from_uid = ? AND to_uid = ? AND status = 0;";
    std::shared_ptr<sql::PreparedStatement> state(
        con->_con->prepareStatement(sql));
    state->setInt(1, fromuid);
    state->setInt(2, touid);
    // 执行更新操作
    int rows = state->executeUpdate();
    if (rows == 0) {
      std::cout << "更新好友申请表错误！\n";
      con->_con->rollback();
      return false;
    }
    // 查询发送好友申请者填写的备注信息
    sql = "SELECT remark "
          "FROM friend_apply "
          "WHERE from_uid = ? AND to_uid = ?;";
    state.reset(con->_con->prepareStatement(sql));
    state->setInt(1, fromuid);
    state->setInt(2, touid);
    std::shared_ptr<sql::ResultSet> res(state->executeQuery());
    std::string self_remark = "";
    if (!res->next()) {
      std::cout << "from " << fromuid << " to" << touid
                << " friend apply not find remark。" << std::endl;
      con->_con->rollback();
      return false;
    }
    self_remark = res->getString("remark");
    sql = "INSERT INTO "
          "friend(self_id, friend_id, remark) VALUES(?, ?, ?),(?,?,?);";
    state.reset(con->_con->prepareStatement(sql));
    state->setInt(1, fromuid);
    state->setInt(2, touid);
    state->setString(3, self_remark);
    state->setInt(4, touid);
    state->setInt(5, fromuid);
    state->setString(6, remark);
    rows = state->executeUpdate();
    if (rows == 0) {
      std::cout << "好友表加入数据错误！\n";
      con->_con->rollback();
      return false;
    }
    // 提交修改
    con->_con->commit();
    return true;
    // 更新成功 添加好友到好友列表
  } catch (sql::SQLException &e) {
    con->_con->rollback();
    std::cerr << "SQLException: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ",SQLState: " << e.getSQLState() << " )" << std::endl;
    return false;
  }
}

bool MysqlDao::GetFriends(
    int uid, std::vector<std::shared_ptr<FriendInfo>> &friendList) {
  auto con = _pool->getConnection();
  if (con == nullptr) {
    return false;
  }
  Defer defer([this, &con]() {
    if (con.get() != nullptr) {
      con->_con->setAutoCommit(true);
      _pool->returnConnect(std::move(con));
    }
  });
  try {
    // 获取好友列表
    std::string sql =
        "SELECT * FROM"
        "(SELECT friend_id, remark FROM friend WHERE self_id = ?) "
        "AS friendsid LEFT JOIN user ON friendsid.friend_id =user.uid;";
    std::shared_ptr<sql::PreparedStatement> state(
        con->_con->prepareStatement(sql));
    state->setInt(1, uid);
    // 执行查询操作
    std::shared_ptr<sql::ResultSet> res(state->executeQuery());
    std::cout << "GetFriends\n";
    while (res->next()) {
      std::shared_ptr<FriendInfo> friendInfo = std::make_shared<FriendInfo>();
      friendInfo->uid = res->getInt("uid");
      friendInfo->name = res->getString("name");
      friendInfo->email = res->getString("email");
      friendInfo->desc = "";
      friendInfo->remark = res->getString("remark");
      friendList.push_back(friendInfo);
    }
    return true;
  } catch (sql::SQLException &e) {
    std::cerr << "SQLException: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ",SQLState: " << e.getSQLState() << " )" << std::endl;
    return false;
  }
}

bool MysqlDao::SaveMessage(std::shared_ptr<TextMessage> textMessage) {
  auto con = _pool->getConnection();
  if (con == nullptr) {
    return false;
  }
  Defer defer([this, &con]() {
    if (con.get() != nullptr) {
      con->_con->setAutoCommit(true);
      _pool->returnConnect(std::move(con));
    }
  });
  try {
    std::string sql =
        "INSERT INTO message(msg_id, fromuid, touid, message, send_time)"
        " VALUES(?, ?, ?, ?, ?);";
    std::shared_ptr<sql::PreparedStatement> state(
        con->_con->prepareStatement(sql));
    state->setString(1, textMessage->msg_id);
    state->setInt(2, textMessage->fromuid);
    state->setInt(3, textMessage->touid);
    state->setString(4, textMessage->message);
    state->setString(5, textMessage->send_time);
    int rows = state->executeUpdate();
    if (rows == 0) {
      std::cout << "Save message failed." << std::endl;
      return false;
    }
    return true;
  } catch (sql::SQLException &e) {
    std::cerr << "SQLException: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ",SQLState: " << e.getSQLState() << " )" << std::endl;
    return false;
  }
}

void MysqlDao::SureMessage(int touid,
                           const std::vector<std::string> &msg_id_list) {
  auto con = _pool->getConnection();
  if (con == nullptr) {
    return;
  }
  Defer defer([this, &con]() {
    if (con.get() != nullptr) {
      con->_con->setAutoCommit(true);
      _pool->returnConnect(std::move(con));
    }
  });
  try {
    std::string msg_ids = "";
    for (int i = 0; i < msg_id_list.size(); i++) {
      msg_ids.append("'");
      msg_ids.append(msg_id_list[i]);
      msg_ids.append("',");
    }
    msg_ids.pop_back();
    std::stringstream ss;
    ss << "UPDATE message SET status = 1 WHERE touid =" << touid
       << " AND msg_id IN(" << msg_ids << ");";
    std::string sql = ss.str();
    std::shared_ptr<sql::PreparedStatement> state(
        con->_con->prepareStatement(sql));
    int rows = state->executeUpdate();
    if (rows != msg_id_list.size()) {
      std::cout << "Sure message received failed." << std::endl;
      return;
    }
    return;
  } catch (sql::SQLException &e) {
    std::cerr << "SQLException: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ",SQLState: " << e.getSQLState() << " )" << std::endl;
    return;
  }
}

void MysqlDao::LoadMessage(
    int touid, std::vector<std::shared_ptr<TextMessage>> &messageList) {
  auto con = _pool->getConnection();
  if (con == nullptr) {
    return;
  }
  Defer defer([this, &con]() {
    if (con.get() != nullptr) {
      con->_con->setAutoCommit(true);
      _pool->returnConnect(std::move(con));
    }
  });
  try {
    std::string sql = "SELECT * FROM message "
                      "WHERE touid = ? AND status = 0 ORDER BY send_time ASC;";
    std::shared_ptr<sql::PreparedStatement> state(
        con->_con->prepareStatement(sql));
    state->setInt(1, touid);
    std::shared_ptr<sql::ResultSet> res(state->executeQuery());
    while (res->next()) {
      std::shared_ptr<TextMessage> textMessage =
          std::make_shared<TextMessage>();
      textMessage->msg_id = res->getString("msg_id");
      textMessage->fromuid = res->getInt("fromuid");
      textMessage->touid = res->getInt("touid");
      textMessage->message = res->getString("message");
      textMessage->send_time = res->getString("send_time");
      messageList.push_back(textMessage);
    }
    return;
  } catch (sql::SQLException &e) {
    std::cerr << "SQLException: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ",SQLState: " << e.getSQLState() << " )" << std::endl;
    return;
  }
}